package com.jemmyee.pizza.web.controller;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
import org.springframework.web.servlet.ModelAndView;

import com.jemmyee.core.utils.DateUtils;
import com.jemmyee.pizza.entity.Excerpt;
import com.jemmyee.pizza.service.IExcerptService;
import com.jemmyee.pizza.web.Constants;
import com.jemmyee.pizza.web.bean.ExcerptBean;

@Controller
@RequestMapping("/admin/excel")
public class ExcelController {

	private static final Log log = LogFactory.getLog(ExcelController.class);

	@Autowired
	private IExcerptService excerptService;

	@RequestMapping("/toImportExcel/{messageType}")
	public ModelAndView toImportExcel(HttpServletRequest request, @PathVariable
	Integer messageType) {

		ModelAndView mv = new ModelAndView();

		mv.setViewName("/admin/excel/importExcel");
		mv.addObject("message", this.getMessage(messageType));

		return mv;
	}

	@RequestMapping("/importExcel")
	public ModelAndView importExcel(HttpServletRequest request, Integer gasType) {
		ModelAndView mv = new ModelAndView();
		File file = null;
		try {

			MultipartHttpServletRequest multipartrequest = (MultipartHttpServletRequest) request;

			CommonsMultipartFile obj = (CommonsMultipartFile) multipartrequest
					.getFile("file");
			file = new File("/tmp.xls");

			byte[] bytes = obj.getBytes();
			FileOutputStream fos;

			fos = new FileOutputStream(file);
			fos.write(bytes);
			fos.flush();
			fos.close();

			this.readExcel(file, gasType);

			mv.addObject("location", "/admin/excel/toImportExcel/1");
			mv.setViewName("/admin/excel/redirect");
		} catch (Exception e) {
			e.printStackTrace();
			mv.addObject("location", "/admin/excel/toImportExcel/2");
			return mv;
		} finally {
			if (file.exists())
				file.delete();
		}

		return mv;

	}
	/**
	 * 读取excel并将数据存入数据库
	 * @param file
	 * @param gasType
	 * @throws Exception
	 */
	public void readExcel(File file, Integer gasType) throws Exception {
		StringBuffer sb = new StringBuffer();

		Workbook wb = Workbook.getWorkbook(file);

		if (wb == null)
			return;

		// 获得了Workbook对象之后，就可以通过它得到Sheet（工作表）对象了
		Sheet[] sheet = wb.getSheets();

		List<Excerpt> excerpts = null;

		if (sheet != null && sheet.length > 0) {
			// 对每个工作表进行循环
			for (int i = 0; i < sheet.length; i++) {
				// 得到当前工作表的行数
				int rowNum = sheet[i].getRows();
				for (int j = 1; j < rowNum; j++) {

					Cell[] cells = sheet[i].getRow(j);

					if (cells[0].getContents() == null
							|| "".equals(cells[0].getContents().trim()))
						continue;

					Excerpt excerpt = new Excerpt();
					excerpt.setPersonNumber(cells[0].getContents());
					excerpt.setPersonName(cells[1].getContents());
					excerpt.setAddress(cells[2].getContents());
					excerpt.setReadAmount(Double.parseDouble(cells[3]
							.getContents()));
					excerpt.setGasAmount(Double.parseDouble(cells[4]
							.getContents()));
					excerpt.setGasMoney(Double.parseDouble(cells[5]
							.getContents()));
					excerpt.setExcerptTime(DateUtils.parseStringToDateWithFormat((cells[6].getContents().substring(0, 4)+"-"+cells[6].getContents().substring(4, 6)+"-"+cells[6].getContents().substring(6, 8)),"yyyy-MM-dd"));

					excerpt.setPersonCard(cells[7].getContents());
					excerpt.setPersonPhone(cells[8].getContents());
					
					excerpt.setGasType(gasType);
					excerpt.setPayStatus(0);

					if (excerpts == null)
						excerpts = new ArrayList<Excerpt>();
					excerpts.add(excerpt);

				}
			}
		}

		if (excerpts != null && excerpts.size() > 0) {
			excerptService.saveOrUpdateAll(excerpts);
		}

		// 最后关闭资源，释放内存
		wb.close();
	}

	@RequestMapping("/exportExcel")
	public ModelAndView exportExcel(HttpServletRequest request,
			HttpServletResponse response) {

		ExcerptBean excerptBean = new ExcerptBean();
		try {
			if (request.getSession().getAttribute(
					Constants.SESSION_PAGE_SYS_QUERY_BEAN) != null) {
				excerptBean = (ExcerptBean) request.getSession().getAttribute(
						Constants.SESSION_PAGE_SYS_QUERY_BEAN);
			}
		} catch (Exception e) {

		}
		DetachedCriteria dc = DetachedCriteria.forClass(Excerpt.class);

		if (excerptBean.getName() != null) {
			dc.add(Restrictions.like("personName", excerptBean.getName(),
					MatchMode.ANYWHERE));
		}

		if (excerptBean.getNumber() != null) {
			dc.add(Restrictions.like("personNumber", excerptBean.getNumber(),
					MatchMode.ANYWHERE));
		}

		if (excerptBean.getType() != null) {
			dc.add(Restrictions.eq("gasType", excerptBean.getType()));
		}
		// 发布时间
		if (excerptBean.getStartTime() != null) {
			dc.add(Restrictions.ge("excerptTime", excerptBean.getStartTime()));
		}
		if (excerptBean.getEndTime() != null) {
			dc.add(Restrictions.le("excerptTime", excerptBean.getEndTime()));
		}

		dc.addOrder(Order.desc("excerptTime"));

		List<Excerpt> result = excerptService.findByCriteria(dc);

		
		try {
		
			String filename = new String("用户气费信息.xls".getBytes( "UTF-8"), "iso-8859-1"); 
			
			response.reset();// 清空输出流

			response.setHeader("Content-disposition", "attachment; filename=" + filename);
			
			response.setContentType("application/vnd.ms-excel;charset=utf-8");

			this.createExcel(response.getOutputStream(), result);
			
		} catch (UnsupportedEncodingException e) {
			
			e.printStackTrace();
		}
		catch (IOException e) {
			
			e.printStackTrace();
		}

		return null;
	}

	
	/**
	 * 根据查询结果生成excel并写入到响应流中
	 * @param os
	 * @param list
	 */
	public void createExcel(OutputStream os, List<Excerpt> list) {
		WritableWorkbook wwb = null;
		try {
			// 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
			wwb = Workbook.createWorkbook(os);
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		if (wwb != null) {
			
			try {
				// 创建一个可写入的工作表
				// Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
				WritableSheet ws = wwb.createSheet("sheet1", 0);

				//在Excel中，第一个参数表示列，第二个表示行
				Label titleA = new Label(0, 0, "用户姓名");
				Label titleB = new Label(1, 0, "用户账户");
				Label titleC = new Label(2, 0, "气费");
				Label titleD = new Label(3, 0, "地址");
				Label titleE = new Label(4, 0, "电话");
				// 将生成的单元格添加到工作表中
				ws.addCell(titleA);
				ws.addCell(titleB);
				ws.addCell(titleC);
				ws.addCell(titleD);
				ws.addCell(titleE);
				for(int i = 0 ; i < list.size() ; i++){
					Excerpt excerpt = list.get(i);
					Label labelA = new Label(0, i+1, excerpt.getPersonName());
					Label labelB = new Label(1, i+1, excerpt.getPersonCard());
					Label labelC = new Label(2, i+1, String.valueOf(excerpt.getGasMoney()));
					Label labelD = new Label(3, i+1, excerpt.getAddress());
					Label labelE = new Label(4, i+1, excerpt.getPersonPhone());
					ws.addCell(labelA);
					ws.addCell(labelB);
					ws.addCell(labelC);
					ws.addCell(labelD);
					ws.addCell(labelE);
				}
				
				
				// 从内存中写入文件中
				wwb.write();
				// 关闭资源，释放内存
				wwb.close();
			} catch (RowsExceededException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} catch (WriteException e) {
				e.printStackTrace();
			}
		}
	}

	public String getMessage(int messageType) {

		if (messageType == 1) {
			return "导入成功！";
		} else if (messageType == 2) {
			return "导入失败！";
		} else {
			return "";
		}

	}
}
